package com.tvb.infoapp.database;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

import com.tvb.infoapp.object.CLSB_Field;
import com.tvb.infoapp.object.CLSB_Rec_Field;
import com.tvb.infoapp.object.CLSB_Record;
import com.tvb.infoapp.object.CLSB_Structure;

public class DatabaseHelperP {
    private static final String TABLE_RECORD = "clsb_record";

    private static final String KEY_RECORD_ID = "id";

    private static final String KEY_RECORD_STRUCTURE_ID = "structure_id";

    private static final String KEY_RECORD_TITLE = "title";

    private static final String KEY_RECORD_TYPE = "type";

    private static final String KEY_RECORD_PATH = "path";

    private static final String KEY_RECORD_DES = "des";

    private static final String KEY_RECORD_CONTENT = "content";

    private static final String KEY_RECORD_KEYWORD = "keyword";

    // Table clsb_structure

    private static final String TABLE_STRUCTURE = "clsb_structure";

    private static final String KEY_STRUCTURE_ID = "id";

    private static final String KEY_STRUCTURE_NAME = "name";

    private static final String KEY_STRUCTURE_DES = "des";

    // Table clsb_field_ftructure
    private static final String TABLE_FIELD_STRUCTURE = "clsb_field_ftructure";

    private static final String KEY_FIELD_STRUCTURE_ID = "id";

    private static final String KEY_FIELD_STRUCTURE_STRUCTURE_ID = "structure_id";

    private static final String KEY_FIELD_STRUCTURE_FIELD_NAME = "field_name";

    private static final String KEY_FIELD_STRUCTURE_FIELD_TYPE = "field_type";

    private static final String KEY_FIELD_STRUCTURE_SEARCHABLE = "searchable";

    private static final String KEY_FIELD_STRUCTURE_SORTABLE = "sortable";

    // Table clsb_record_field

    private static final String TABLE_RECORD_FIELD = "clsb_record_field";

    private static final String KEY_RECORD_FIELD_RECORD_ID = "record_id";

    private static final String KEY_RECORD_FIELD_FIELD_ID = "field_id";

    private static final String KEY_RECORD_FIELD_VALUE = "value";

    // Table clsb_record_relation

    private static final String TABLE_RECORD_RELATION = "clsb_record_relation";

    private static final String KEY_RECORD_RELATION_RECORD_ID = "record_id";

    private static final String KEY_RECORD_RELATION_MAP_ID = "map_id";

    private static SQLiteDatabase db;

    public DatabaseHelperP() {
        
        File root_file = new File(Environment.getExternalStorageDirectory().getAbsoluteFile() + "/TVB/");
        if (!root_file.exists()) {
            root_file.mkdirs();
        }
        File db_file = new File(root_file, "infoapp.sqlite");
        if (!db_file.exists()) {
            try {
                db_file.createNewFile();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        db = SQLiteDatabase.openDatabase(db_file.getAbsolutePath(), null,
                SQLiteDatabase.OPEN_READWRITE);
        try {
            createTable();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void createTable() {
        String CREAT_RECORD = "CREATE TABLE " + TABLE_RECORD + "("
                + KEY_RECORD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_RECORD_STRUCTURE_ID + " INTEGER," + KEY_RECORD_TITLE
                + " TEXT," + KEY_RECORD_TYPE + " TEXT," + KEY_RECORD_PATH
                + " TEXT," + KEY_RECORD_DES + " TEXT," + KEY_RECORD_CONTENT
                + " TEXT," + KEY_RECORD_KEYWORD + " TEXT)";
        db.execSQL(CREAT_RECORD);

        // Creat Table clsb_structure
        String CREAT_STRUCTURE = "CREATE TABLE " + TABLE_STRUCTURE + "("
                + KEY_STRUCTURE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_STRUCTURE_NAME + " TEXT," + KEY_STRUCTURE_DES + " TEXT )";
        db.execSQL(CREAT_STRUCTURE);

        // Creat table clsb_field_structure

        String CREAT_FIELD_STRUCTURE = "CREATE TABLE " + TABLE_FIELD_STRUCTURE
                + "(" + KEY_FIELD_STRUCTURE_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_FIELD_STRUCTURE_STRUCTURE_ID + " INTEGER,"
                + KEY_FIELD_STRUCTURE_FIELD_NAME + " TEXT,"
                + KEY_FIELD_STRUCTURE_FIELD_TYPE + " TEXT,"
                + KEY_FIELD_STRUCTURE_SEARCHABLE + " INTEGER,"
                + KEY_FIELD_STRUCTURE_SORTABLE + " INTEGER )";
        db.execSQL(CREAT_FIELD_STRUCTURE);

        // Creat table clsb_record_field

        String CREAT_RECORD_FIELD = "CREATE TABLE " + TABLE_RECORD_FIELD + "("
                + KEY_RECORD_FIELD_RECORD_ID + " INTEGER,"
                + KEY_RECORD_FIELD_FIELD_ID + " INTEGER,"
                + KEY_RECORD_FIELD_VALUE + " TEXT)";
        db.execSQL(CREAT_RECORD_FIELD);

        // Creat table clsb_record_relation
        String CREAT_RECORD_RELATION = "CREATE TABLE " + TABLE_RECORD_RELATION
                + "(" + KEY_RECORD_RELATION_RECORD_ID + " INTEGER,"
                + KEY_RECORD_RELATION_MAP_ID + " INTEGER)";
        db.execSQL(CREAT_RECORD_RELATION);
    }

    // insert record
    public void insertRecord(CLSB_Record record) {

        ContentValues values = new ContentValues();
        values.put(KEY_RECORD_STRUCTURE_ID, record.getStructure_id());
        values.put(KEY_RECORD_TITLE, record.getTitle());
        values.put(KEY_RECORD_TYPE, record.getType());
        values.put(KEY_RECORD_PATH, record.getPath());
        values.put(KEY_RECORD_DES, record.getDes());
        values.put(KEY_RECORD_CONTENT, record.getContent());
        values.put(KEY_RECORD_KEYWORD, record.getKey_word());
        db.insert(TABLE_RECORD, null, values);

    }

    // insert structure
    public void insertStructure(CLSB_Structure structure) {

        ContentValues values = new ContentValues();
        values.put(KEY_STRUCTURE_NAME, structure.getName());
        values.put(KEY_STRUCTURE_DES, structure.getDes());
        db.insert(TABLE_STRUCTURE, null, values);

    }

    // insert field
    public void insertField(CLSB_Field field) {

        ContentValues values = new ContentValues();
        values.put(KEY_FIELD_STRUCTURE_STRUCTURE_ID, field.getStructure_id());
        values.put(KEY_FIELD_STRUCTURE_FIELD_NAME, field.getName());
        values.put(KEY_FIELD_STRUCTURE_FIELD_TYPE, field.getType());
        values.put(KEY_FIELD_STRUCTURE_SEARCHABLE, field.getSearchable());
        values.put(KEY_FIELD_STRUCTURE_SORTABLE, field.getSortable());
        db.insert(TABLE_FIELD_STRUCTURE, null, values);

    }

    // insert record_field
    public void insertRecordField(int record_id, int field_id, String value) {

        ContentValues values = new ContentValues();
        values.put(KEY_RECORD_FIELD_RECORD_ID, record_id);
        values.put(KEY_RECORD_FIELD_FIELD_ID, field_id);
        values.put(KEY_RECORD_FIELD_VALUE, value);
        db.insert(TABLE_RECORD_FIELD, null, values);
    }

    // get all structure

    public List<CLSB_Structure> getAllStructure() {
        List<CLSB_Structure> list_structure = new ArrayList<CLSB_Structure>();

        String query = "SELECT * FROM " + TABLE_STRUCTURE;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                list_structure.add(new CLSB_Structure(cursor.getInt(0), cursor
                        .getString(1), cursor.getString(2)));
            } while (cursor.moveToNext());
        }
        return list_structure;
    }

    public List<CLSB_Field> getListFieldByStructure(int structure_id) {
        List<CLSB_Field> list_field = new ArrayList<CLSB_Field>();

        String query = "SELECT * FROM " + TABLE_FIELD_STRUCTURE + " WHERE "
                + KEY_FIELD_STRUCTURE_STRUCTURE_ID + "=" + structure_id;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                list_field.add(new CLSB_Field(cursor.getInt(0), cursor
                        .getInt(1), cursor.getString(2), cursor.getString(3),
                        cursor.getInt(4), cursor.getInt(5)));
            } while (cursor.moveToNext());
        }
        return list_field;
    }

    public List<CLSB_Record> getAllRecord() {
        List<CLSB_Record> list_record = new ArrayList<CLSB_Record>();

        String query = "SELECT * FROM " + TABLE_RECORD;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                list_record.add(new CLSB_Record(cursor.getInt(0), cursor
                        .getInt(1), cursor.getString(2), cursor.getString(3),
                        cursor.getString(4), cursor.getString(5), cursor
                                .getString(6), cursor.getString(7)));
            } while (cursor.moveToNext());
        }
        return list_record;
    }

    public boolean checkRecordDataBase() {
        boolean result = false;

        String query = "SELECT * FROM " + TABLE_RECORD;
        Cursor cursor = db.rawQuery(query, null);
        Log.i("aaaaaaaaa", "" + cursor.getCount());
        if (cursor.getCount() > 0) {
            result = true;
        } else {
            result = false;
        }

        return result;
    }
    
    public CLSB_Record selectRecord (int id) {
        CLSB_Record record = new CLSB_Record();
        String query = "SELECT * FROM " + TABLE_RECORD 
                + " WHERE " + KEY_RECORD_ID + "=" + id;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                record = new CLSB_Record(cursor.getInt(0), cursor.getInt(1),
                        cursor.getString(2), cursor.getString(3), cursor.getString(4),
                        cursor.getString(5), cursor.getString(6), cursor.getString(7));
            } while (cursor.moveToNext());
        }
        // get list id field;
        List<CLSB_Rec_Field> list_field = new ArrayList<CLSB_Rec_Field>();
        String query_field = "SELECT * FROM " + TABLE_FIELD_STRUCTURE
                + " WHERE " + KEY_FIELD_STRUCTURE_STRUCTURE_ID + "=" + record.getStructure_id();
        Cursor cursor_field = db.rawQuery(query_field, null);
        if (cursor_field.moveToFirst()) {
            do {
                list_field.add(new CLSB_Rec_Field(cursor_field.getInt(0), cursor_field.getString(2), ""));
            } while (cursor_field.moveToNext());
        }
        
        for (int i = 0; i < list_field.size(); i ++) {
            CLSB_Rec_Field rec_field = list_field.get(i);
            String query_value = "SELECT * FROM " + TABLE_RECORD_FIELD
                     + " WHERE " + KEY_RECORD_FIELD_FIELD_ID + "=" + rec_field.getField_id()
                     + " AND " + KEY_RECORD_FIELD_RECORD_ID + "=" + record.getId();
            Cursor cursor_rec_field = db.rawQuery(query_value, null);
            if (cursor_rec_field.moveToFirst()) {
                do {
                    list_field.get(i).setValue(cursor_rec_field.getString(2));
                } while (cursor_rec_field.moveToNext());
            }
        }
        record.setList_field(list_field);
        return record;
    }
    
    public List<CLSB_Record> getListRecordByKeyword (String keyword){
        List<CLSB_Record> list_record = new ArrayList<CLSB_Record>();
        String query = "SELECT * FROM " + TABLE_RECORD
                + " WHERE " + KEY_RECORD_TITLE + " LIKE '%" + keyword + "%'";
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                list_record.add(new CLSB_Record(cursor.getInt(0), cursor.getInt(1),
                        cursor.getString(2), cursor.getString(3), cursor.getString(4),
                        cursor.getString(5), cursor.getString(6), cursor.getString(7)));
            } while (cursor.moveToNext());
        }
        return list_record;
    }

}
